package com.example.util.excel;


import com.alibaba.druid.util.StringUtils;
import org.apache.poi.POIXMLDocumentPart;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.*;
import org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTMarker;

import java.io.FileOutputStream;
import java.io.IOException;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Collectors;

/**
 * @author wangkai
 * @Description: TODO(excel工具)
 * @date 2019年1月29日
 */
public class ExcelUtils {

    /**
     * 获取单元格数据
     *
     * @param cell
     * @return
     */
    public static String getCellValueByCell(Cell cell) {
        // 判断是否为null或空串
        if (cell == null || cell.toString().trim().equals("")) {
            return "";
        }
        String cellValue = "";
        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_STRING: // 字符串类型
                if (cell.getStringCellValue() != null) {
                    cellValue = cell.getStringCellValue().trim();
                    cellValue = StringUtils.isEmpty(cellValue) ? "" : cellValue;
                }
                break;
            case Cell.CELL_TYPE_BOOLEAN: // 布尔类型
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            case Cell.CELL_TYPE_FORMULA: // 公式
                cellValue = String.format("%s", cell.getCellFormula());
                break;
            case Cell.CELL_TYPE_NUMERIC: // 数值类型
                if (HSSFDateUtil.isCellDateFormatted(cell)) { // 判断日期类型
                    SimpleDateFormat sdp = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    if (cell.getDateCellValue() != null) {
                        cellValue = sdp.format(cell.getDateCellValue());
                    }
                } else { // 否
                    cellValue = new DecimalFormat("#.######").format(cell.getNumericCellValue());
                }
                break;
            default: // 其它类型，取空串吧
                cellValue = "";
                break;
        }
        return cellValue;
    }

    /**
     * 获取excel中图片
     * //03的方法获取图片
     *
     * @param sheetNum
     * @param sheet
     * @param workbook
     * @return
     * @throws InvalidFormatException
     * @throws IOException
     */
    public static Map<String, ExcelImgData> getSheetPictrues03Map(int sheetNum, HSSFSheet sheet, HSSFWorkbook workbook) {
        Map<String, ExcelImgData> result = new HashMap<String, ExcelImgData>();
        List<HSSFPictureData> pictures = workbook.getAllPictures();
        if (pictures.size() != 0) {
            for (HSSFShape shape : sheet.getDrawingPatriarch().getChildren()) {
                HSSFClientAnchor anchor = (HSSFClientAnchor) shape.getAnchor();
                if (shape instanceof HSSFPicture) {
                    ExcelImgData sheetIndexPicMap = new ExcelImgData();
                    HSSFPicture pic = (HSSFPicture) shape;
                    int pictureIndex = pic.getPictureIndex() - 1;
                    HSSFPictureData picData = pictures.get(pictureIndex);
                    sheetIndexPicMap.setRow(anchor.getRow2());
                    sheetIndexPicMap.setCol(anchor.getCol2());
                    sheetIndexPicMap.setData(picData);
                    result.put(String.format("%s", anchor.getRow2()), sheetIndexPicMap);
                }
            }
        }
        return result;
    }

    /**
     * 07的方法获取图片
     *
     * @param sheetNum
     * @param sheet
     * @param workbook
     * @return
     */
    public static Map<String, ExcelImgData> getSheetPictrues07Map(int sheetNum, XSSFSheet sheet, XSSFWorkbook workbook) {
        Map<String, ExcelImgData> result = new HashMap<String, ExcelImgData>();
        for (POIXMLDocumentPart dr : sheet.getRelations()) {
            if (dr instanceof XSSFDrawing) {
                XSSFDrawing drawing = (XSSFDrawing) dr;
                List<XSSFShape> shapes = drawing.getShapes();
                for (XSSFShape shape : shapes) {
                    ExcelImgData sheetIndexPicMap = new ExcelImgData();
                    XSSFPicture pic = (XSSFPicture) shape;
                    XSSFClientAnchor anchor = pic.getPreferredSize();
                    CTMarker ctMarker = anchor.getFrom();
                    sheetIndexPicMap.setRow(ctMarker.getRow());
                    sheetIndexPicMap.setCol(ctMarker.getCol());
                    sheetIndexPicMap.setData(pic.getPictureData());
                    result.put(String.format("%s", ctMarker.getRow()), sheetIndexPicMap);
                }
            }
        }
        return result;
    }


    /**
     * 实体导出到excel
     * @param filePath
     * @param datas
     * @throws IOException
     * @throws IllegalAccessException
     */
    public static void exportExcel(String filePath, List<?> datas) throws IOException, IllegalAccessException {
        //创建工作薄对象
        HSSFWorkbook workbook = new HSSFWorkbook();//这里也可以设置sheet的Name
        BigDecimal sb = new BigDecimal(datas.size());
        BigDecimal eb = new BigDecimal(50000);
        int num = sb.divide(eb, 0, BigDecimal.ROUND_CEILING).intValue();
        int count = 0;
        Field[] fields = datas.get(count).getClass().getDeclaredFields();
        List<Field> listField = Arrays.asList(fields).stream()
                .filter(field -> field.getAnnotation(Excel.class) != null)
                .collect(Collectors.toList());
        List<Excel> excels = listField.stream()
                .map(field -> field.getAnnotation(Excel.class)).collect(Collectors.toList());
        for (int i = 0; i < num; i++) {
            //创建工作表对象
            HSSFSheet sheet = workbook.createSheet();
            int m = datas.size() - count;
            HSSFRow row = sheet.createRow(0);
            for (int f = 0; f < excels.size(); f++) {
                row.createCell(f).setCellValue(excels.get(f).name());
            }
            for (int j = 0; j < m; j++) {
                HSSFRow row1 = sheet.createRow(j + 1);
                for (int f = 0; f < listField.size(); f++) {
                    Field field = listField.get(f);
                    field.setAccessible(true);
                    Excel excel = field.getAnnotation(Excel.class);
                    String dateFormat = excel.dateFormat();
                    String readConverterExp = excel.readConverterExp();

                    Cell cell = row1.createCell(f);
                    cell.setCellType(excel.cellType().value());
                    if (!StringUtils.isEmpty(dateFormat)) {
                        SimpleDateFormat sdf = new SimpleDateFormat(dateFormat);
                        cell.setCellValue(sdf.format((Date) field.get(datas.get(count))));
                    } else if (!StringUtils.isEmpty(readConverterExp)) {
                        Map<String, String> map = new HashMap<>();
                        String[] strs = readConverterExp.split(",");
                        if (strs.length > 0) {
                            Arrays.asList(strs).forEach(str -> {
                                String[] st = str.split("=");
                                if (st.length > 0) {
                                    map.put(st[0], st[1]);
                                }
                            });
                        }
                        String read = (String) field.get(datas.get(count));
                        if (map.size() > 0) {
                            cell.setCellValue(String.format("%s%s", map.get(read), excel.suffix()));
                        } else {
                            if (read == null) {
                                read = excel.defaultValue();
                            }
                            cell.setCellValue(String.format("%s%s", read, excel.suffix()));
                        }
                    } else {
                        String read = (String) field.get(datas.get(count));
                        if (read == null) {
                            read = excel.defaultValue();
                        }
                        cell.setCellValue(String.format("%s%s", read, excel.suffix()));
                    }
                    if (excel.combo().length > 0 || !StringUtils.isEmpty(excel.prompt())) {
                        DVConstraint constraint = DVConstraint
                                .createCustomFormulaConstraint("BB1");
                        if (excel.combo().length > 0) {
                            // 加载下拉列表内容
                            constraint = DVConstraint
                                    .createExplicitListConstraint(excel.combo());
                        }
                        // 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
                        CellRangeAddressList regions = new CellRangeAddressList(j + 1,
                                j + 1, f, f);
                        // 数据有效性对象
                        HSSFDataValidation data_validation_list = new HSSFDataValidation(
                                regions, constraint);
                        if (!StringUtils.isEmpty(excel.prompt())) {
                            data_validation_list.createPromptBox(excel.prompt(), excel.prompt());
                        }
                        sheet.addValidationData(data_validation_list);
                    }


                }
            }
            workbook.setSheetName(i, String.format("%s%s", "sheet", i + 1));//设置sheet的Name
        }
        //文档输出
        FileOutputStream out = new FileOutputStream(String.format("%s%s%s", filePath, new SimpleDateFormat("yyyyMMddHHmmss").format(new Date()).toString(), ".xls"));
        workbook.write(out);
        out.flush();
        out.close();
    }

}
